from typing import ParamSpecKwargs
from src.core.database import DatabaseConnector
from src.core.utils import *
import datetime
import re

class DBHelper:
    def __init__(self, db_connector: DatabaseConnector = None):
        self.db_connector = db_connector if db_connector else DatabaseConnector()

    def get_owner(self, identifier):
        """获取单位"""
        try:
            # 尝试直接作为ID
            owner_id = int(identifier)
            sql = "SELECT * FROM owners WHERE owner_id = %s"
            return self.db_connector.fetchone(sql, (owner_id,))
        except ValueError:
            # 作为名称查询
            sql = "SELECT * FROM owners WHERE owner_name = %s"
            return self.db_connector.fetchone(sql, (identifier,))
    
    def get_ipdomain(self, ip, domain):
        if not is_valid_ip(ip):
            raise ValueError(f"错误：无效的IP地址格式: {ip}")
        if not is_valid_domain(domain):
            raise ValueError(f"错误：无效的域名格式: {domain}")
        sql = "SELECT * FROM ipdomains WHERE ip = %s AND domain = %s"
        return self.db_connector.fetchone(sql, (ip, domain))

    def get_domain(self, identifier):
        """获取指定域名信息"""
        try:
            domain_id = int(identifier)
            sql = "SELECT * FROM domains d LEFT JOIN owners o ON d.owner_id = o.owner_id WHERE domain_id = %s"
            params = [domain_id]
        except ValueError:
            # 检验域名格式
            if not is_valid_domain(identifier):
                raise ValueError(f"错误：无效的域名格式: {identifier}")
            sql = "SELECT * FROM domains d LEFT JOIN owners o ON d.owner_id = o.owner_id WHERE d.domain = %s"
            params = [identifier]

        return self.db_connector.fetchone(sql, params)

    def get_ip(self, identifier):
        try:
            ip_id = int(identifier)
            sql = "SELECT * FROM ips left join owners on ips.owner_id = owners.owner_id WHERE ip_id = %s"
            return self.db_connector.fetchone(sql, (ip_id,))
        except ValueError:
            # 检测IP地址格式
            if not is_valid_ip(identifier):
                raise ValueError(f"错误：无效的IP地址格式: {identifier}")
            sql = "SELECT * FROM ips left join owners on ips.owner_id = owners.owner_id WHERE ip = %s"
            return self.db_connector.fetchone(sql, (identifier,))

    def get_or_add_owner(self, owner_name, parent_identifier=None, parent_relation=None):
        """获取或添加单位"""
        if not owner_name:
            raise ValueError("错误：单位名称不能为空")
        owner = self.get_owner(owner_name)
        if owner:
            return owner

        # 正则判断不是数字
        if re.match(r'^\d+$', owner_name):
            raise ValueError(f"错误：单位名称不能是ID值以外的数字: {owner_name}")

        parent_id = None
        if parent_identifier:
            parent = self.get_or_add_owner(parent_identifier)
            parent_id = parent['owner_id']

        # 添加单位
        current_time = datetime.datetime.now()
        sql = "INSERT INTO owners (owner_name, create_time, update_time) VALUES (%s, %s, %s)"
        if self.db_connector.execute_query(sql, (owner_name, current_time, current_time)):
            print(f"添加单位: {owner_name} 成功")
            new_owner = self.get_owner(owner_name)
            new_owner['_is_new'] = True
            if parent_id:
                sql = "INSERT INTO owner_relations (child_id, parent_id, parent_type) VALUES (%s, %s, %s)"
                if not self.db_connector.execute_query(sql, (new_owner['owner_id'], parent_id, parent_relation)):
                    print(f"添加单位关系: {owner_name} -> {parent_identifier} 失败")
            return new_owner
        else:
            raise ValueError(f"添加单位: {owner_name} 失败")
        
    def get_or_add_domain(self, domain_name, owner_id=None):
        """获取或添加域名"""
        if not domain_name:
            raise ValueError("错误：域名不能为空")
        domain = self.get_domain(domain_name)
        if domain:
            return domain
        # 添加域名
        if not is_valid_domain(domain_name):
            raise ValueError(f"错误：无效的ID或域名格式: {domain_name}")
        current_time = datetime.datetime.now()
        sql = "INSERT INTO domains (domain, owner_id, create_time, update_time) VALUES (%s, %s, %s, %s)"
        if self.db_connector.execute_query(sql, (domain_name, owner_id, current_time, current_time)):
            print(f"添加域名: {domain_name} 成功")
            new_domain = self.get_domain(domain_name)
            new_domain['_is_new'] = True
            return new_domain
        else:
            raise ValueError(f"添加域名: {domain_name} 失败")

    def get_or_add_ip(self, ip_address, owner_id=None):
        """获取或添加IP"""
        if not ip_address:
            raise ValueError("错误：IP不能为空")
        ip = self.get_ip(ip_address)
        if ip:
            return ip
        # 添加IP
        if not is_valid_ip(ip_address):
            raise ValueError(f"错误：无效的ID或IP地址格式: {ip_address}")
        current_time = datetime.datetime.now()
        sql = "INSERT INTO ips (ip, owner_id, create_time, update_time) VALUES (%s, %s, %s, %s)"
        if self.db_connector.execute_query(sql, (ip_address, owner_id, current_time, current_time)):
            print(f"添加IP: {ip_address} 成功")
            new_ip = self.get_ip(ip_address)
            new_ip['_is_new'] = True
            return new_ip
        else:
            raise ValueError(f"添加IP: {ip_address} 失败")

    def get_or_add_ipdomain(self, ip_address, domain_name):
        ip = self.get_or_add_ip(ip_address)
        domain = self.get_or_add_domain(domain_name)
        ipdomain = self.get_ipdomain(ip['ip'], domain['domain'])
        if ipdomain:
            return ipdomain
        # 添加ipdomain
        current_time = datetime.datetime.now()
        sql = "INSERT INTO ipdomains (ip, domain, create_time, update_time) VALUES (%s, %s, %s, %s)"
        if self.db_connector.execute_query(sql, (ip['ip'], domain['domain'], current_time, current_time)):
            print(f"添加IP: {ip_address} 域名: {domain_name} 成功")
            new_ipdomain = self.get_ipdomain(ip['ip'], domain['domain'])
            new_ipdomain['_is_new'] = True
            return new_ipdomain
        else:
            raise ValueError(f"添加IP: {ip_address} 域名: {domain_name} 失败")

    def get_or_add_ips(self, ip_addresses, owner_id=None):
        """获取或添加IP"""
        # 如果ip_addresses为空，则返回空列表
        if not ip_addresses:
            return []
        # 如果不是列表
        if not isinstance(ip_addresses, list):
            ip_addresses = ip_addresses.split(',')

        result = []
        for ip_address in ip_addresses:
            ip_address = ip_address.strip().lower()
            if ip_address == '':
                continue
            # 判断是否为IP
            ip = self.get_or_add_ip(ip_address, owner_id)
            result.append(ip)
        return result

    def check_ipdomain(self, ip_address, domain_name, is_valid = True):
        ipdomain = self.get_or_add_ipdomain(ip_address, domain_name)
        current_time = datetime.datetime.now()
        sql = "UPDATE ipdomains SET is_valid = %s, last_check_time = %s, update_time = %s WHERE ip = %s AND domain = %s"
        if self.db_connector.execute_query(sql, (is_valid, current_time, current_time, ipdomain['ip'], ipdomain['domain'])):
            print(f"更新IP: {ip_address} 域名: {domain_name} 状态为 {'有效' if is_valid else '无效'} 成功")
        else:
            raise Exception(f"更新IP: {ip_address} 域名: {domain_name} 状态为 {'有效' if is_valid else '无效'} 失败")

    def add_ip_or_domain(self, ip_or_domains, owner_identifier=None):
        """添加IP或域名"""
        # 先检测owner信息
        owner_id = None
        if owner_identifier:
            owner = self.get_or_add_owner(owner_identifier)
            if owner:
                owner_id = owner['owner_id']
                print(f"将为 {owner['owner_name']} 添加IP或域名: ")
        
        for ip_or_domain in ip_or_domains:
            ip_or_domain = ip_or_domain.strip().lower()
            if ip_or_domain == '':
                continue
            # 判断是否为IP
            current_time = datetime.datetime.now()
            if is_valid_ip(ip_or_domain):
                ip = self.get_ip(ip_or_domain)
                if ip:
                    print(f"  IP: {ip_or_domain} 已存在")
                    if ip['owner_id'] != owner_id:
                        # 更新归属
                        sql = "UPDATE ips SET owner_id = %s, update_time = %s WHERE ip_id = %s"
                        self.db_connector.execute_query(sql, (owner_id, current_time, ip['ip_id']))
                        print(f"  更新IP: {ip_or_domain} 归属成功")
                    continue
                # 添加IP
                sql = "INSERT INTO ips (ip, owner_id, create_time, update_time) VALUES (%s, %s, %s, %s)"
                if self.db_connector.execute_query(sql, (ip_or_domain, owner_id, current_time, current_time)):
                    print(f"  添加IP: {ip_or_domain} 成功")
                else:
                    print(f"  添加IP: {ip_or_domain} 失败")
            elif is_valid_domain(ip_or_domain):
                domain = self.get_domain(ip_or_domain)
                if domain:
                    print(f"  域名: {ip_or_domain} 已存在")
                    if domain['owner_id'] != owner_id:
                        # 更新归属
                        sql = "UPDATE domains SET owner_id = %s, update_time = %s WHERE domain_id = %s"
                        self.db_connector.execute_query(sql, (owner_id, current_time, domain['domain_id']))
                        print(f"  更新域名: {ip_or_domain} 归属成功")
                    continue
                # 添加域名
                sql = "INSERT INTO domains (domain, owner_id, create_time, update_time) VALUES (%s, %s, %s, %s)"
                if self.db_connector.execute_query(sql, (ip_or_domain, owner_id, current_time, current_time)):
                    print(f"  添加域名: {ip_or_domain} 成功")
                else:
                    print(f"  添加域名: {ip_or_domain} 失败")
            else:
                raise ValueError(f"错误：无效的IP或域名格式: {ip_or_domain}")

    

    